package my.db.DAO;
import java.sql.*;
import java.util.ArrayList;

import javax.naming.Context;
import javax.sql.DataSource;

import org.apache.catalina.connector.Request;

import my.db.DTO.*;
import config.EstablishConnection;

public class ArticleDAO {
	private PreparedStatement pstmt=null;
	private Connection con=null;
	private ResultSet rs=null;
	private Statement stmt;
	private AuthenticationDTO auDTO=null;
	EstablishConnection conn=null;
	private Context cnt=null;
	private DataSource ds=null;
	/*
	 * Constructor Initialized
	 */
	public ArticleDAO(){
		conn=EstablishConnection.CreateInstance();
	}
	/*
	 * Getting only account password
	 *  if true 
	 *  	return object AuthenticationDTO
	 *  else 
	 *  	return null
	 */
	public AuthenticationDTO getUseAccount(String pwd){
		String query = "Select ACCOUNTPASSWORD From Account Where ACCOUNTPASSWORD=?";
		AuthenticationDTO dto=null;
		try{		
			pstmt = conn.conection.prepareStatement(query);
			pstmt.setString(1,pwd);
			rs=pstmt.executeQuery();
			if(rs.next()){
				dto=new AuthenticationDTO();
				dto.setAccountPassword("AccountPassword");
			}			
			return dto;
		}catch(SQLException e){
			//System.out.println(e.getMessage());
			return null;
		}
		//return null;
	}
	/*
	 * Getting  article info and author info 
	 *  if true 
	 *  	return object Arraylist of ArticleDTO
	 *  else 
	 *  	return null
	 */
	public ArrayList<ArticleDTO> getListArticle(){
		ArrayList<ArticleDTO> ls=new ArrayList<ArticleDTO>();
		ResultSet r=null;
		String sql="select a.ArticleNO,a.ArticleTitle,u.username,att.articlename from Articles a,Users u,ArticleType att where u.userid=a.userid and att.articletypeid=a.articletypeid and a.articlestatus=1";
		try{
			pstmt=conn.conection.prepareStatement(sql);
			r=pstmt.executeQuery();
			while(r.next()){				
			}
			return ls;
		}catch(SQLException e){
			return null;
		}
	}
	/*
	 * Inserting new article 
	 *  if true 
	 *  	return true
	 *  else 
	 *  	return false
	 */
	public boolean addArticle(ArticleDTO article) {
		String sql="Insert Into Articles Values(ARTICLENO.nextval,?,?,?,?,?,?,?)";
		try {
			pstmt=conn.conection.prepareStatement(sql);
			pstmt.setString(1, article.getArticleTitle());
			pstmt.setDate(2, new java.sql.Date(article.getArticleDate().getTime()));
			pstmt.setString(3, article.getArticleContent());
			pstmt.setInt(4,  article.getAuthor().getAuthorID());
			pstmt.setInt(5, article.getArticleType().getTypeID());			
			pstmt.setInt(6,1);
			pstmt.setString(7,article.getArticleImage());
			pstmt.executeUpdate();
			pstmt.close();
			return true;
		} catch (SQLException e) {
			return false;
		}
	}
	/*
	 * Getting  article and article type by specific article id 
	 *  if true 
	 *  	return object Arraylist of ArticleDTO
	 *  else 
	 *  	return null
	 */
	public ArrayList<ArticleDTO> getArticleAuthor(int id){
		ArticleDTO a=null;
		ArrayList<ArticleDTO> lst_a=new ArrayList<ArticleDTO>();
		String sql=" Select A.ArticleNo,A.ArticleTitle,A.ArticleDate,T.ArticleName From Articles A,ArticleType T Where A.ArticleTypeID=T.ArticletypeID And UserID=? And ArticleStatus=1 Order By ArticleNo DESC";
		try {
			pstmt=conn.conection.prepareStatement(sql);
			pstmt.setInt(1, id);
			ResultSet r=pstmt.executeQuery();
			while(r.next()){
				a=new ArticleDTO();			
				ArticleTypeDTO adto=new ArticleTypeDTO();
				a.setArticleNo(r.getInt(1));
				a.setArticleTitle(r.getString(2));
				a.setArticleDate(r.getDate(3));
				adto.setTypeName(r.getString(4));
				a.setArticleType(adto);			
				lst_a.add(a);
				a=null;
			}
			return lst_a;
		} catch (SQLException e) {
			return null;
		}
	}
	/*
	 * Getting total rows of data
	 *  if true 
	 *  	return number of row 
	 *  	
	 */
	public int countRows(String keyword) throws SQLException{
		int num = 0;
		String sql="";
		if (keyword == null || keyword == ""){
			sql = "select count(ArticleNO) from Articles where articlestatus=1";
		}else{
			sql = "select count(ArticleNO) from Articles where articlestatus=1 and "
				+ "lower(ArticleNo) like '%" + keyword.toLowerCase() + "%' or "
				+ "lower(ArticleTitle) like '%"+keyword.toLowerCase() +"%'";
		}
		
		pstmt = conn.conection.prepareStatement(sql);
		rs = pstmt.executeQuery(sql);
		if (rs.next()) {
			num = rs.getInt(1);
		}
		return num;
	}
	/*
	 * Listing pagination 
	 *  if true 
	 *  	return object Arraylist of ArticleDTO
	 *  else 
	 *  	return null
	 */
	public ArrayList<ArticleDTO> listPagination (int start, int stop, String keyword) throws SQLException{
		ArrayList<ArticleDTO> list = new ArrayList<ArticleDTO>();
		ArticleDTO dto = null;
		String sql="";
		if (keyword == null || keyword == ""){
			System.out.println("No search");
			sql = "select A.* from (select T.*, rownum as rn from (select articleno,articletitle,articleimage from articles where articlestatus=1) T order by ArticleNo desc) A where rn between "+start+" and "+stop;
		}else{
			keyword=keyword.toUpperCase();
			String subSql = "select articleno,articletitle,articleimage from articles where articlestatus=1 and UPPER(articletitle) like '%"+keyword+"%'";
			
			sql = "select A.* from (select T.*, rownum as rn from "
				+ "(" + subSql + ") T order by ArticleNo desc) "
				+ "A where rn between " + start  + " and " + stop;
		}
		pstmt=conn.conection.prepareStatement(sql);
		rs = pstmt.executeQuery();
		while (rs.next()){
			dto=new ArticleDTO();
			dto.setArticleNo(rs.getInt(1));
			dto.setArticleTitle(rs.getString(2));
			dto.setArticleImage(rs.getString(3));
			
			list.add(dto);
			dto=null;
		}
		return list;
	}
	
	public int countRowsArticleAuthor(String keyword,int userid) throws SQLException{
		int num = 0;
		String sql="";
		if (keyword == null || keyword == ""){
			sql = "select count(ArticleNO) from Articles where articlestatus=1 and userid="+userid;
		}else{
			sql = "select count(ArticleNO) from Articles where articlestatus=1 and userid="+userid+" and upper(ArticleNo) like '%" + keyword + "%' or upper(ArticleTitle) like '%"+keyword +"%'";
		}
		
		pstmt = conn.conection.prepareStatement(sql);
		rs = pstmt.executeQuery(sql);
		if (rs.next()) {
			num = rs.getInt(1);
		}
		
		return num;
	}
	public ArrayList<ArticleDTO> ArticlePaginationAuthor (int start, int stop, String keyword,int userId) throws SQLException{
		ArrayList<ArticleDTO> list = new ArrayList<ArticleDTO>();
		ArticleDTO dto = null;
		String sql="";
		if (keyword == null || keyword == ""){
			System.out.println("No search");
			sql = "select A.* from (select T.*, rownum as rn from (select articleno,articletitle,articledate from articles where articlestatus=1 and userid="+userId+") T order by ArticleNo desc) A where rn between "+start+" and "+stop;
		}else{
			keyword=keyword.toUpperCase();
			String subSql = "select articleno,articletitle,articledate from articles where articlestatus=1 and userid="+userId+" and UPPER(articletitle) like '%"+keyword+"%'";
			
			sql = "select A.* from (select T.*, rownum as rn from "
				+ "(" + subSql + ") T order by ArticleNo desc) "
				+ "A where rn between " + start  + " and " + stop;
		}
		pstmt=conn.conection.prepareStatement(sql);
		rs = pstmt.executeQuery();
		while (rs.next()){
			dto=new ArticleDTO();
			dto.setArticleNo(rs.getInt(1));
			dto.setArticleTitle(rs.getString(2));
			dto.setArticleDate(rs.getDate(3));
			
			list.add(dto);
			dto=null;
		}
		return list;
	}
	/*
	 * Editing and Update Article info by specific article number
	 *  if true 
	 *  	return true
	 *  else 
	 *  	return false
	 */
	public boolean editArticle(ArticleDTO article) {
		String sql="Update Articles Set ArticleTitle=?,ArticleContent=?,ArticleTypeID=? Where ArticleNo=?";
		try {
			pstmt=conn.conection.prepareStatement(sql);
			pstmt.setString(1, article.getArticleTitle());
			pstmt.setString(2, article.getArticleContent());
			pstmt.setInt(3,article.getArticleType().getTypeID());
			pstmt.setInt(4, article.getArticleNo());
			pstmt.executeUpdate();
			pstmt.close();
			return true;
		} catch (SQLException e) {
			return false;
		}
	}
	/*
	 * Removing one or multi article by specifc article number
	 *  if true 
	 *  	return true
	 *  else 
	 *  	return false
	 */
	public boolean delete(String id){
		String sql="Update Articles Set ArticleStatus=1-ArticleStatus WHERE ArticleNo In("+id+")";	
		try{
			pstmt=conn.conection.prepareStatement(sql);		
			pstmt.executeUpdate();
			pstmt.close();
			return true;
			}catch(SQLException e){	
				return false;
			}
	}
	/*
	 * Getting article info , user info , and article type info 
	 *  if true 
	 *  	return object of ArticleDTO
	 *  else 
	 *  	return null
	 */
	public ArticleDTO ReadArticle(int id){
		String sql="select a.ArticleNO,a.ArticleTitle,u.username,att.articletypeId,att.articlename,a.articledate,a.articleContent from Articles a,Users u,ArticleType att where u.userid=a.userid and att.articletypeid=a.articletypeid and a.ArticleNO=?";
		AuthorDTO adto=null;
		ArticleTypeDTO at=null;
		ArticleDTO dto=null;
		try{
			pstmt=conn.conection.prepareStatement(sql);
			pstmt.setInt(1, id);
			
			rs=pstmt.executeQuery();
			if(rs.next()){
				
				adto=new AuthorDTO();
				adto.setAuthorName(rs.getString(3));	
				
				at=new ArticleTypeDTO();
				at.setTypeID(rs.getInt(4));
				at.setTypeName(rs.getString(5));	
				
				dto=new ArticleDTO();
				dto.setArticleNo(rs.getInt(1));
				dto.setArticleTitle(rs.getString(2));
				dto.setAuthor(adto);
				dto.setArticleDate(rs.getDate(6));
				dto.setArticleContent(rs.getString(7));
				dto.setArticleType(at);	
				
			}
			return dto;
		}catch(SQLException e){
			return null;
		}finally{
			
		}
	}
	/*
	 * Finding article  by searching date
	 *  if true 
	 *  	return object Arraylist of ArticleDTO
	 *  else 
	 *  	return null
	 */
public ArrayList<ArticleDTO> search(String startDate,String endDate){
	String sql="select a.ArticleNO,a.ArticleTitle,u.username,att.articletypeId,att.articlename,a.articledate,a.articleContent from Articles a,Users u,ArticleType att where u.userid=a.userid and att.articletypeid=a.articletypeid  and ArticleDate >= TO_DATE(?, 'yyyy/MM/dd') and ArticleDate <= TO_DATE(?, 'yyyy/MM/dd')";
	AuthorDTO adto=null;
	ArticleTypeDTO at=null;
	ArticleDTO dto=null;
	ArrayList<ArticleDTO>ls=new ArrayList<ArticleDTO>();
	try{
		pstmt=conn.conection.prepareStatement(sql);
		pstmt.setString(1, startDate);
		pstmt.setString(2, endDate);
		rs=pstmt.executeQuery();
		while(rs.next()){
			adto=new AuthorDTO();
			adto.setAuthorName(rs.getString(3));	
			at=new ArticleTypeDTO();
			at.setTypeID(rs.getInt(4));
			at.setTypeName(rs.getString(5));	
			dto=new ArticleDTO();
			dto.setArticleNo(rs.getInt(1));
			dto.setArticleTitle(rs.getString(2));
			dto.setAuthor(adto);
			dto.setArticleDate(rs.getDate(6));
			dto.setArticleContent(rs.getString(7));
			dto.setArticleType(at);	
			ls.add(dto);
		}		
		return ls;
	}catch(SQLException e){
		return null;
	}
	}
	
	public ArrayList<ArticleDTO> mostRecent(){
		ArrayList<ArticleDTO> lst=new ArrayList<>();
		String sql="select * from(select a.*, rownum rnum from (select ArticleNo,ArticleTitle from Articles where articlestatus=1  order by ArticleDate desc)a where rownum <=15)where rnum>= 1";
		ArticleDTO dto=null;
		try{
			pstmt=conn.conection.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				dto=new ArticleDTO();
				dto.setArticleNo(rs.getInt(1));
				dto.setArticleTitle(rs.getString(2));
				lst.add(dto);
				dto=null;
			}		
			return lst;
		}catch(SQLException e){
			return null;
		}
	}
}
